package com.gree.flowable.app.services.impl;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.google.gson.JsonObject;
import com.gree.flowable.app.mapper.*;
import com.gree.flowable.app.mapper.dao.FormDataDao;
import com.gree.flowable.app.mapper.plus.FormBusinessInfoService;
import com.gree.flowable.app.mapper.plus.FormModelService;
import com.gree.flowable.app.pojo.ex.StartPiExIForm;
import com.gree.flowable.app.pojo.form.*;
import com.gree.flowable.app.pojo.model.StartProcessInstanceMO;
import com.gree.flowable.app.pojo.vo.ReturnVO;
import com.gree.flowable.app.services.ICreateTableService;
import com.gree.flowable.app.services.IFlowableProcessInsService;
import com.gree.flowable.app.tools.constants.MySQLColumnsEnum;
import com.gree.flowable.app.tools.constants.OracleColumnsEnum;
import com.gree.flowable.app.tools.constants.ReturnCode;
import com.gree.flowable.app.tools.utils.DuridConfig;
import com.gree.flowable.app.tools.utils.GetDataSourceConfig;
import net.sf.ehcache.search.query.QueryManager;
import org.flowable.engine.RuntimeService;
import org.flowable.engine.runtime.Execution;
import org.flowable.engine.runtime.ProcessInstance;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.util.StringUtils;

import java.security.Key;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import static com.alibaba.fastjson.JSON.toJSONString;

/**
 * @author ：180849
 * @date ：Created in 2021/4/26 16:22
 * @description：
 * @modified By：
 * @version: 1.0
 */
@Service
public class ICreateTableServiceImpl implements ICreateTableService {

    @Autowired
    private CreateTablesMapper createTablesMapper;

    @Autowired
    private DuridConfig duridConfig;

    @Autowired
    private GetDataSourceConfig getDataSourceConfig;

    @Autowired
    private FormInfoMapper formInfoMapper;
    @Autowired
    private DataSourceTransactionManager dataSourceTransactionManager;

    @Autowired
    private TransactionDefinition transactionDefinition;

    @Autowired
    private IFlowableProcessInsService iFlowableProcessInsService;

    @Autowired
    private FormDataDao formDataDao;

    @Autowired
    private FormModelMapper formModelMapper;

    @Autowired
    private FormModelService formModelService;

    @Autowired
    private RuntimeService runtimeService;

    @Autowired
    private FormBusinessMapper formBusinessMapper;

    @Autowired
    private FormBusinessInfoMapper formBusinessInfoMapper;

    @Autowired
    private FormBusinessInfoService formBusinessInfoService;

    @Autowired
    private IFlwFormNodesetBusinessServiceImpl flwFormNodesetBusinessService;

    @Override
    public ReturnVO createTable(String tableName, List<CreateTables> list) {
        TransactionStatus transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);

        int total = 0;
        ReturnVO result;
        //判断数据库类型
        String url = duridConfig.getUrl();
        boolean isMysql = url.contains("mysql");
        try {
            /*if (isMysql){
                //创建表
                createTableDao(tableName, list);
                //判断是否创建成功
                String name = createTablesMapper.queryTabelName(tableName);
                if (!StringUtils.isEmpty(name)){
                    //0是未同步，1未同步的
                    QueryWrapper<FormInfo> infoQueryWrapper = new QueryWrapper<>();
                    FormInfo formInfo = new FormInfo();
                    formInfo.setFormSync(1);
                    infoQueryWrapper.lambda().eq(FormInfo::getFormId,tableName);
                    total = formInfoMapper.update(formInfo, infoQueryWrapper);
                } else {
                    new Exception("数据库建表失败");
                }
            } else {
                System.out.println("此为Oracle方法");
                //创建表
                createTableDaoOracle(tableName,list);
                //判断是否创建成功
                String name = createTablesMapper.queryTableNameInOracle(tableName);
                if (!StringUtils.isEmpty(name)){
                    //0是未同步，1未同步的
                    QueryWrapper<FormInfo> infoQueryWrapper = new QueryWrapper<>();
                    FormInfo formInfo = new FormInfo();
                    formInfo.setFormSync(1);
                    infoQueryWrapper.lambda().eq(FormInfo::getFormId,tableName);
                    total = formInfoMapper.update(formInfo, infoQueryWrapper);
                } else {
                    new Exception("数据库建表失败");
                }
            }*/
            List<FormModel> formModelList = new LinkedList<>();
            for (CreateTables createTables : list) {
                QueryWrapper<FormModel> formModelQueryWrapper = new QueryWrapper<>();
                FormModel formModel = new FormModel();
                formModel.setFieldType(createTables.getType());
//                formModel.setFields(createTables.getFields());
//                formModel.setFormId(tableName);
                formModelQueryWrapper.lambda()
                        .eq(FormModel::getFields,createTables.getFields())
                        .eq(FormModel::getFormId,tableName);
//                formModelList.add(formModel);
                int update = formModelMapper.update(formModel, formModelQueryWrapper);
                total = total+update;
            }
//            if (total == list.size()){
                QueryWrapper<FormInfo> infoQueryWrapper = new QueryWrapper<>();
                FormInfo formInfo = new FormInfo();
                formInfo.setFormSync(1);
                infoQueryWrapper.lambda().eq(FormInfo::getFormId,tableName);
                formInfoMapper.update(formInfo, infoQueryWrapper);
//            }
//            boolean updateStatus = formModelService.updateBatchById(formModelList);
//            Integer totals = formModelMapper.saveFieldType(formModelList);
            dataSourceTransactionManager.commit(transactionStatus);
            result = new ReturnVO(ReturnCode.SUCCESS, "表创建成功",total);
        } catch (Exception e) {
            e.printStackTrace();
            dataSourceTransactionManager.rollback(transactionStatus);
            result = new ReturnVO(ReturnCode.FAIL, "表创建失败,原因："+e.getMessage(),total);
        }

        return result;
    }

    @Override
    public ReturnVO queryTableColumns(String formId) {
        ReturnVO result = null;
        //判断数据库类型
        String url = duridConfig.getUrl();
        boolean isMysql = url.contains("mysql");
        try {
            if(isMysql){
                List<MySQLColumnVO> mySQLColumnVOS = createTablesMapper.queryColumnsInMySQL(formId);

                List<CreateTables> createTables = new ArrayList<>();
                mySQLColumnVOS.forEach(item->{
                    CreateTables iTable = new CreateTables();
                    iTable.setFields(item.getColumnName());
                    iTable.setLabel(item.getColumnComment());
                    iTable.setLength(item.getCharacterLength());
                    iTable.setDeft(item.getColumnDefault());
                    iTable.setType(MySQLColumnsEnum.getEnumByName(item.getDataType(), item.getCharacterLength()));
                    iTable.setPrimary("PRI".equals(item.getColumnKey())? 1:0);
                    iTable.setAllownull("YES".equals(item.getIsNullable())? 1:0);
                    createTables.add(iTable);
                });
                result = new ReturnVO(ReturnCode.SUCCESS, "OK", createTables);

            }else {
                System.out.println(formId.toUpperCase());
                List<OracleColumnVo> columnInOracleList = createTablesMapper.queryColumnInOracle(formId.toUpperCase());
                List<CreateTables> createTables = new ArrayList<>();
                for (OracleColumnVo oracleColumnVo : columnInOracleList) {
                    CreateTables tables = new CreateTables();
                    tables.setFields(oracleColumnVo.getColumnName().toLowerCase());
                    tables.setLabel(oracleColumnVo.getComments());
                    String dataType = oracleColumnVo.getDataType();
                    if ("NUMBER".equals(dataType)){
                        tables.setLength(oracleColumnVo.getDataPrecision());
                    } else {
                        tables.setLength(oracleColumnVo.getDataLength());
                    }
                    tables.setDeft(oracleColumnVo.getDataDefault());
                    tables.setPrimary("yes".equals(oracleColumnVo.getColumnKey())? 1:0);
                    tables.setAllownull("Y".equals(oracleColumnVo.getNullAble())? 1:0);
                    tables.setType(OracleColumnsEnum.getEnumByName(oracleColumnVo.getDataType(),tables.getLength()));
                    createTables.add(tables);
                }
                result = new ReturnVO(ReturnCode.SUCCESS, "OK", createTables);
            }

        }catch (Exception e){
            e.printStackTrace();
            result = new ReturnVO(ReturnCode.FAIL, "表查询失败,原因："+e.getMessage());
        }
        return result;
    }

    @Override
    public ReturnVO youCanDoIt(StartPiExIForm params) {
        ReturnVO result = null;

        /* 1、 先拿取创建流程实例的参数 */
        StartProcessInstanceMO startMO = params.getStartProcessInstanceMO();

        String businessKey = startMO.getBusinessKey();
        /* 2、校验是否已存在流程实例 */
        int count = iFlowableProcessInsService.countBusinessKey(businessKey);
        if (count > 0){
            return new ReturnVO(ReturnCode.FAIL, "创建单号失败，不能新建已存在的单号！");
        }
        // 事务开启！
        TransactionStatus transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);

        /* 3、实例化流程！ */
        ReturnVO<ProcessInstance> flowMsg = iFlowableProcessInsService.startProcessInstanceByKey(startMO);
        if (flowMsg.getCode().equals(ReturnCode.FAIL)){
            return new ReturnVO(ReturnCode.FAIL, flowMsg.getMsg());
        }

//        Execution execution = runtimeService.createExecutionQuery().processInstanceBusinessKey(businessKey).singleResult();



        /* 4、拿取实例化表单参数 */
        IFormDataVO iFormVO = params.getiFormDataVO();
        String formId = iFormVO.getFormId();
        //添加流程实例与表单的关系
        FormBusiness formBusiness = new FormBusiness();
        formBusiness.setBusinessKey(businessKey);
        formBusiness.setFormId(formId);
        int formBusinessCount = formBusinessMapper.insert(formBusiness);
        List<FormBusinessInfo> formBusinessInfoList = new LinkedList<>();
        try {

            Map<String, Object> data = iFormVO.getData();
            for (String key : data.keySet()) {
                //判断是否为上传文件的组件
                QueryWrapper<FormModel> queryWrapper = new QueryWrapper<>();
                queryWrapper.lambda().eq(FormModel::getFormId,formId).eq(FormModel::getFields,key);
                FormModel formModel = formModelMapper.selectOne(queryWrapper);
                //如果为上传文件的组件，将String转为List
                if ("upload-file".equals(formModel.getShape())){
                    String uploadInfo = JSONObject.toJSONString(data.get(key));
                    data.put(key,uploadInfo);
                }
                //如果为多选框，将ArrayList转为String做数据存储
                if ("checkbox".equals(formModel.getShape())){
                    String checkBoxInfo =  JSONObject.toJSONString(data.get(key));
                    data.put(key,checkBoxInfo);
                }
                //添加表单信息
                String fieldType = formModel.getFieldType();
                FormBusinessInfo formBusinessInfo = new FormBusinessInfo();
                formBusinessInfo.setBusinessKey(businessKey);
                formBusinessInfo.setFields(key);
                formBusinessInfo.setFieldType(formModel.getFieldType());
                switch (fieldType){
                    case "Date":
                    case "Time":
                        formBusinessInfo.setLvaldate(Long.valueOf((data.get(key).toString())));
                        break;
                    case "Integer":
                    case "Double":
                        formBusinessInfo.setValnum(Double.valueOf((data.get(key)).toString()));
                        break;
                    case "String":
                    default:
                        formBusinessInfo.setValstr(/*JSONObject.toJSONString(data.get(key))*/data.get(key).toString());
                }
                formBusinessInfoList.add(formBusinessInfo);
            }
            boolean fbiCount = formBusinessInfoService.saveBatch(formBusinessInfoList);
//            runtimeService.setVariables(execution.getId(), data);

            /* 插入进程中的节点控制表    */
            String processKey = params.getStartProcessInstanceMO().getProcessDefinitionKey();
            boolean b = flwFormNodesetBusinessService.addFormNodeBusiness(processKey, formId, businessKey);
            if (!b){
                throw new Exception("插入表单与流程节点进程表失败");
            }
            /* 5、无情插入数据 */
//            int i = formDataDao.addFormData(businessKey, formId, data);
            /* 6、做 个 了 断 ！*/
            if (fbiCount){
                result = new ReturnVO<>(ReturnCode.SUCCESS,"表单创建成功！");
                dataSourceTransactionManager.commit(transactionStatus);
            } else {
                result = new ReturnVO<>(ReturnCode.FAIL,"表单创建失败，数据库未同步？");
                dataSourceTransactionManager.rollback(transactionStatus);
            }
        }catch (Exception e){
            e.printStackTrace();
            result = new ReturnVO<>(ReturnCode.FAIL,e.getMessage());
            dataSourceTransactionManager.rollback(transactionStatus);
        }
        return result;
    }

    /**
     * MySQL数据库
     * @param tableName     表名
     * @param columnList    字段信息
     * @throws Exception
     */
    public void createTableDao(String tableName, List<CreateTables> columnList) throws Exception {
        //创建连接
        Connection conn = DriverManager.getConnection(duridConfig.getUrl(), duridConfig.getUsername(), duridConfig.getPassword());
        Statement statement = conn.createStatement();
        StringBuilder sql = new StringBuilder("create table " + tableName + "( ");
        String str;
        String dbType;
        StringBuilder primaryKey = new StringBuilder();
        for (int i = 0; i < columnList.size(); i++) {
            CreateTables column = columnList.get(i);

            String fields = column.getFields();
            //判断类型
            String type = column.getType();
            dbType = MySQLColumnsEnum.getColumnsByType(type);
            Integer length = column.getLength();
            String label = column.getLabel();

            if (StringUtils.isEmpty(column.getLength())){
                length = MySQLColumnsEnum.getLengThByType(type);
            }

            if (length > 0 ){
                str = " " + fields + " " + dbType + "(" + length + ")";
            } else {
                str = " " + fields + " " + dbType;
            }


            //设置是否非空
            if (0 == column.getAllownull()) {
                str = str + " not null ";
            }
            //设置默认值
            if (!StringUtils.isEmpty(column.getDeft())){
                str = str + " default " + column.getDeft() ;
            }
            str = str + " comment '"+label+"' ,";
            //拼接主键
            if (1 == column.getPrimary()) {
                if (StringUtils.isEmpty(primaryKey.toString())){
                    primaryKey.append(fields);
                } else {
                    primaryKey.append(",").append(fields);
                }
            }
            if (i == columnList.size() - 1 && !StringUtils.isEmpty(primaryKey)) {
                str = str + " primary key ( " + primaryKey.toString() + " )";
            }
            sql.append(str);

        }
        sql.append(" )");
        boolean execute = statement.execute(sql.toString());

    }


    public void createTableDaoOracle(String tableName,List<CreateTables> columnList) throws Exception {
        //创建连接
        Connection conn = DriverManager.getConnection(getDataSourceConfig.getUrl(), getDataSourceConfig.getUsername(), getDataSourceConfig.getPassword());
        Statement statement = conn.createStatement();
        StringBuilder sql = new StringBuilder("create table " + tableName + "( ");
        String str;
        String dbType;
        StringBuilder primaryKey = new StringBuilder();
        String tags;
        List<String> tagsList = new LinkedList<>();
        for (int i = 0; i < columnList.size(); i++) {
            CreateTables column = columnList.get(i);

            String fields = column.getFields();
            //判断类型
            String type = column.getType();
            dbType = OracleColumnsEnum.getColumnsByType(type);
            Integer length = column.getLength();
            String label = column.getLabel();

            if (StringUtils.isEmpty(column.getLength())){
                length = OracleColumnsEnum.getLengThByType(type);
            }

            if (length > 0 && !OracleColumnsEnum.Double.toString().equals(type) ){
                str = " " + fields + " " + dbType + "(" + length + ")";
            } else {
                str = " " + fields + " " + dbType;
            }
            //设置默认值
            if (!StringUtils.isEmpty(column.getDeft())){
                str = str + " default " + column.getDeft() ;
            }

            //设置是否非空
            if (0 == column.getAllownull()) {
                str = str + " not null ,";
            } else {
                str = str + " ,";
            }

            tags = "COMMENT ON COLUMN "+ tableName + "."+ fields + " IS '" + label+ "' ";
            tagsList.add(tags);
            //拼接主键
            if (1 == column.getPrimary()) {
                if (StringUtils.isEmpty(primaryKey.toString())){
                    primaryKey.append(fields);
                } else {
                    primaryKey.append(",").append(fields);
                }
            }
            if (i == columnList.size() - 1 && !StringUtils.isEmpty(primaryKey)) {
                str = str + " primary key ( " + primaryKey.toString() + " )";
            }
            sql.append(str);
        }
        sql.append(" )");
        boolean execute = statement.execute(sql.toString());
        for (String tagSQL : tagsList) {
            statement.execute(tagSQL.toString());
        }

    }
}
